const db = require('./config/index')
const { initDatabaseTables } = require('./config/database-redesign')

// 数据库结构迁移脚本（混合方案）
const migrateToNewStructure = async () => {
  console.log('🚀 开始数据库结构迁移（混合方案）...')

  try {
    // 1. 备份现有数据
    console.log('📦 备份现有数据...')
    const existingUsers = await db.query('SELECT * FROM users')
    console.log(`📊 找到 ${existingUsers.length} 个现有用户`)

    // 2. 创建新的表结构
    console.log('🏗️ 创建新的表结构...')
    await initDatabaseTables()

    // 3. 迁移用户数据（保留管理员需要的字段）
    console.log('👤 迁移用户数据（混合方案）...')
    for (const user of existingUsers) {
      // 检查用户是否已存在（避免重复插入）
      const existingUser = await db.query(
        'SELECT id FROM users WHERE email = ?',
        [user.email]
      )

      if (existingUser.length === 0) {
        // 迁移用户数据，保留管理员需要的字段
        const balance = user.coin_balance || user.coins || user.balance || 0
        const vipStatus = user.is_vip ? 'monthly' : 'none'

        await db.query(
          `
          INSERT INTO users (
            email, username, password_hash, nickname, avatar_url, 
            phone, gender, bio, role, status,
            vip_status, vip_expire_date, level, balance,
            total_recharge, total_consumption, reading_time, last_login_at,
            created_at, updated_at
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `,
          [
            user.email,
            user.username,
            user.password_hash,
            user.nickname,
            user.avatar_url,
            user.phone || null,
            user.gender || 'other',
            user.bio || null,
            user.role || 'user',
            'active', // 默认状态
            vipStatus,
            user.vip_expire_date || null,
            user.level || 1,
            balance,
            user.total_recharge || 0,
            user.total_consumption || 0,
            user.reading_time || 0,
            user.last_login_at || null,
            user.created_at,
            user.updated_at
          ]
        )

        console.log(
          `✅ 迁移用户: ${user.email} (余额: ${balance}, VIP: ${vipStatus})`
        )
      }
    }

    // 4. 创建初始钱包交易记录（如果用户有余额）
    console.log('💰 创建钱包交易记录...')
    for (const user of existingUsers) {
      const newUser = await db.query('SELECT id FROM users WHERE email = ?', [
        user.email
      ])
      if (newUser.length > 0) {
        const userId = newUser[0].id
        const balance = user.coin_balance || user.coins || user.balance || 0

        if (balance > 0) {
          // 创建初始余额记录
          await db.query(
            `
            INSERT INTO wallet_transactions (
              user_id, type, amount, balance_before, balance_after,
              description, created_by, admin_reason, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
          `,
            [
              userId,
              'admin_adjust',
              balance,
              0,
              balance,
              '数据迁移初始余额',
              'admin',
              '从旧系统迁移的用户余额',
              user.created_at
            ]
          )

          console.log(`💳 创建初始余额记录 for ${user.email}: ${balance} 书币`)
        }
      }
    }

    // 5. 创建VIP历史记录
    console.log('👑 创建VIP历史记录...')
    for (const user of existingUsers) {
      if (user.is_vip && user.vip_expire_date) {
        const newUser = await db.query('SELECT id FROM users WHERE email = ?', [
          user.email
        ])
        if (newUser.length > 0) {
          const userId = newUser[0].id
          const expireDate = new Date(user.vip_expire_date)
          const isActive = expireDate > new Date() ? 'active' : 'expired'

          await db.query(
            `
            INSERT INTO user_vip_records (
              user_id, vip_type, start_date, expire_date, amount,
              payment_method, status, created_by, admin_reason, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
          `,
            [
              userId,
              'monthly', // 默认为月费
              user.created_at,
              user.vip_expire_date,
              15.0, // 默认月费价格
              'admin',
              isActive,
              'admin',
              '从旧系统迁移的VIP记录',
              user.created_at
            ]
          )

          console.log(`👑 创建VIP记录 for ${user.email} (状态: ${isActive})`)
        }
      }
    }

    // 6. 验证数据完整性
    console.log('🔍 验证数据完整性...')
    const userCount = await db.query('SELECT COUNT(*) as count FROM users')
    const transactionCount = await db.query(
      'SELECT COUNT(*) as count FROM wallet_transactions'
    )
    const vipRecordCount = await db.query(
      'SELECT COUNT(*) as count FROM user_vip_records'
    )

    // 验证余额数据
    const balanceCheck = await db.query(`
      SELECT 
        COUNT(*) as total_users,
        COUNT(CASE WHEN balance > 0 THEN 1 END) as users_with_balance,
        SUM(balance) as total_balance
      FROM users
    `)

    console.log(`📊 数据验证结果:`)
    console.log(`   用户数量: ${userCount[0].count}`)
    console.log(`   钱包交易记录: ${transactionCount[0].count}`)
    console.log(`   VIP记录数量: ${vipRecordCount[0].count}`)
    console.log(
      `   有余额用户: ${balanceCheck[0].users_with_balance}/${balanceCheck[0].total_users}`
    )
    console.log(`   总余额: ${balanceCheck[0].total_balance} 书币`)

    // 7. 生成清理建议（不自动执行）
    console.log('🧹 生成字段清理建议...')
    const cleanupSQL = `
      -- 混合方案：保留管理员需要的字段，清理不必要的字段
      -- 以下SQL仅供参考，请在验证数据迁移成功后谨慎执行
      
      -- 如果有其他冗余字段可以清理：
      -- ALTER TABLE users DROP COLUMN IF EXISTS old_field_name;
      
      -- 建议保留的字段（管理员需要）：
      -- email, username, nickname, role, status, vip_status, vip_expire_date, 
      -- level, balance, total_recharge, total_consumption, reading_time
    `

    console.log('✅ 数据库结构迁移完成（混合方案）！')
    console.log('🎉 用户表保留了管理员需要的字段，同时创建了详细的历史记录表')

    return {
      success: true,
      userCount: userCount[0].count,
      transactionCount: transactionCount[0].count,
      vipRecordCount: vipRecordCount[0].count,
      usersWithBalance: balanceCheck[0].users_with_balance,
      totalBalance: balanceCheck[0].total_balance,
      cleanupSQL
    }
  } catch (error) {
    console.error('❌ 数据库迁移失败:', error)
    throw error
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  migrateToNewStructure()
    .then(result => {
      console.log('🎊 迁移结果:', result)
      process.exit(0)
    })
    .catch(error => {
      console.error('💥 迁移失败:', error)
      process.exit(1)
    })
}

module.exports = {
  migrateToNewStructure
}
